﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[proc_Statistics_Sales_Count]
AS
BEGIN
	Declare @rq varchar(7)=Convert(varchar(7),getdate(),120)

	Delete From Statistics_Sales_Count where yf=@rq

	;With dept as (
		Select Id,CompanyId from Department where (DName like '%销售%' Or DName like '%商务%')--Or DName like '%销售%'
		union all
		Select  Department.Id,Department.CompanyId from Department join dept on Department.ParentId=dept.Id
	)

	Insert into Statistics_Sales_Count(YF,CompanyId,XSRS)
	Select @rq,CompanyId,Sum(xsrs) as xsrs from 
		(Select Id,CompanyId,Isnull((Select Count(id) from Employee e Where e.DepartmentId=d.Id and e.State<>5),0) xsrs
			From (Select Distinct Id,CompanyId from dept) d) de
	Group by CompanyId

	

END
